package com.tongji.run;

import com.ibeetl.admin.core.util.DateUtil;
import com.ibeetl.admin.core.util.PlatformException;
import com.tongji.entity.宣传员;
import com.tongji.entity.宣传员费用登记表;
import com.tongji.entity.路费差额统计;
import com.tongji.utils.JxlsUtils;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.beetl.sql.core.*;
import org.beetl.sql.core.db.DBStyle;
import org.beetl.sql.core.db.MySqlStyle;
import org.beetl.sql.ext.DebugInterceptor;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

public class 车主路费统计 {

    public static Date dateRangeStart = DateUtil.getDate("2023-03-01");
    public static Date dateRangeEnd = DateUtil.getDate("2023-03-10");
    public static  String dateRange = DateUtil.format(dateRangeStart, "yyyy年MM月dd日") + "至" + DateUtil.format(dateRangeEnd, "yyyy年MM月dd日");
    public static String root = "E:\\文档\\第三方费用统计\\路费\\" + dateRange;

    public static void main(String[] args) {
        ConnectionSource source = ConnectionSourceHelper.getSimple("com.mysql.cj.jdbc.Driver", "jdbc:mysql://127.0.0.1:3308/tongji?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&useInformationSchema=true", "root", "123456");
        DBStyle mysql = new MySqlStyle();
        // sql语句放在classpagth的/sql 目录下
        SQLLoader loader = new ClasspathLoader("/sql");
        // 数据库命名跟java命名一样，所以采用DefaultNameConversion，还有一个是UnderlinedNameConversion，下划线风格的，
        UnderlinedNameConversion nc = new  UnderlinedNameConversion();
        // 最后，创建一个SQLManager,DebugInterceptor 不是必须的，但可以通过它查看sql执行情况
        SQLManager sqlManager = new SQLManager(mysql,loader,source,nc,new Interceptor[]{new DebugInterceptor()});



        String[] xcyType = new String[] {"正常宣传员"};

        for (String type : xcyType) {

            File rootFile = new File(root);
            rootFile.mkdirs();
            //if(true) return ;
            宣传员 xcy = new 宣传员();
            xcy.setType(type);
            List xcyList = sqlManager.template(xcy);
            List lufeiHuizongList = new ArrayList();
            Double jieshaofeiTotal = 0D;
            Double buchongyingyangfeiTotal = 0D;
            Double buchongjieshaofeiTotal = 0D;
            Double buhegelufeiTotal = 0D;
            Double lufeiHejiTotal = 0D;
            Double guanlifeiHejiTotal = 0D;
            Double jianyiqiTotal = 0D;
            Double chaobannianTotal = 0D;
            Double wangliewailufeiTotal = 0D;


            Double lufeiTotalCount = 0D;
            Double guanlifeiTotalCount = 0D;
            Double jieshaofeiTotalCount = 0D;
            Double buchongjieshaofeiTotalCount = 0D;
            Double buchongyingyangfeiTotalCount = 0D;
            Double buhegelufeiTotalCount = 0D;
            Double jianyiqiTotalCount = 0D;
            Double chaobannianTotalCount = 0D;
            Double qitafeiyongTotalCount = 0D;


            int i = 0;
            for (Iterator<宣传员> iterator = xcyList.iterator(); iterator.hasNext();) {

                宣传员 x = iterator.next();

                //未有合并的宣传员
                if (x.getHebingName() == null) {
                    Map lufeiHuizong = new HashMap();

                    String name = x.getName();
                    Map<String, Object> model = new HashMap<>();
                    model.put("dateRange",dateRange);
                    model.put("xuhao",x.getXuhao());
                    model = 获取宣传员统计费用(name,model, sqlManager);

                    lufeiTotalCount = lufeiTotalCount + (Integer)model.get("lufeiCount");
                    guanlifeiTotalCount = guanlifeiTotalCount + (Integer)model.get("guanlifeiCount");
                    jieshaofeiTotalCount = jieshaofeiTotalCount + (Integer)model.get("jieshaofeiCount");
                    buchongjieshaofeiTotalCount = buchongjieshaofeiTotalCount + (Integer)model.get("buchongjieshaofeiCount");
                    buchongyingyangfeiTotalCount = buchongyingyangfeiTotalCount + (Integer)model.get("buchongyingyangfeiCount");
                    buhegelufeiTotalCount = buhegelufeiTotalCount + (Integer)model.get("buhegelufeiCount");
                    jianyiqiTotalCount = jianyiqiTotalCount + (Integer)model.get("jianyiqiCount");
                    chaobannianTotalCount = chaobannianTotalCount + (Integer)model.get("chaobannianCount");
                    qitafeiyongTotalCount = qitafeiyongTotalCount + (Integer)model.get("wangliewailufeiCount");


                    i = i+1;
                    生成宣传费领款单(model,i);


                    String excelTemplate = "excelTemplates/路费费用统计.xls";
                    try(InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(excelTemplate)) {
                        if(is==null) {
                            throw new PlatformException("模板资源不存在："+excelTemplate);
                        }
                        File f = new File(root + "\\宣传员\\" + i + "-" + name + "-费用.xls");
                        f.getParentFile().mkdirs();
                        FileOutputStream os = new FileOutputStream(f);
                        JxlsUtils.exportExcel(is,os,model);
                    } catch (IOException e) {
                        throw new PlatformException(e.getMessage());
                    }

                    Double jieshaofei = model.get("jieshaofei") != null ? ((宣传员费用登记表)model.get("jieshaofei")).get总金额() : 0D;
                    Double buchongyingyangfei = model.get("buchongyingyangfei") != null ? ((宣传员费用登记表)model.get("buchongyingyangfei")).get总金额() : 0D;
                    Double buhegelufei = ((宣传员费用登记表)model.get("buhegelufei")).get总金额() != null ? ((宣传员费用登记表)model.get("buhegelufei")).get总金额() : 0D;
                    Double jianyiqi = model.get("jianyiqi") != null ? ((宣传员费用登记表)model.get("jianyiqi")).get总金额() : 0D;
                    if (jianyiqi == null) jianyiqi = 0D;
                    Double chaobannian = model.get("chaobannian") != null ? ((宣传员费用登记表)model.get("chaobannian")).get总金额() : 0D;
                    Double buchongjieshaofei = model.get("buchongjieshaofei") != null ? ((宣传员费用登记表)model.get("buchongjieshaofei")).get总金额() : 0D;
                    Double wangliewailufei = model.get("wangliewailufei") != null ? ((宣传员费用登记表)model.get("wangliewailufei")).get总金额() : 0D;
                    Double lufeiHeji = (Double)model.get("lufeiHeji");
                    Double guanlifeiHeji = (Double)model.get("guanlifeiHeji");

                    if (StringUtils.equals(name,"曹建涛")) {
                        name = "曹桂勇";
                    }
                    if (StringUtils.equals(name,"张秀春")) {
                        name = "岳良全";
                    }
/*
                    buhegelufei
                            lufeiHeji
                    guanlifeiHeji
                            jieshaofei
                    buchongyingyangfei
                            jianyiqi
                    chaobannian
                            buchongjieshaofei
                    wangliewailufei);
                    */
                    lufeiHuizong.put("name",name);
                    lufeiHuizong.put("xuhao",x.getXuhao());
                    lufeiHuizong.put("jieshaofei",jieshaofei);jieshaofeiTotal = jieshaofeiTotal + jieshaofei;
                    lufeiHuizong.put("buchongyingyangfei",buchongyingyangfei);buchongyingyangfeiTotal = buchongyingyangfeiTotal + buchongyingyangfei;
                    lufeiHuizong.put("buchongjieshaofei",buchongjieshaofei);buchongjieshaofeiTotal = buchongjieshaofeiTotal + buchongjieshaofei;
                    lufeiHuizong.put("buhegelufei",buhegelufei);buhegelufeiTotal = buhegelufeiTotal + buhegelufei;
                    lufeiHuizong.put("lufeiHeji",lufeiHeji);lufeiHejiTotal = lufeiHejiTotal + lufeiHeji;
                    lufeiHuizong.put("guanlifeiHeji",guanlifeiHeji);guanlifeiHejiTotal = guanlifeiHejiTotal + guanlifeiHeji;
                    lufeiHuizong.put("jianyiqi",jianyiqi);jianyiqiTotal = jianyiqiTotal + jianyiqi;
                    lufeiHuizong.put("chaobannian",chaobannian);chaobannianTotal = chaobannianTotal + chaobannian;
                    lufeiHuizong.put("wangliewailufei",wangliewailufei);wangliewailufeiTotal = wangliewailufeiTotal + wangliewailufei;

                    lufeiHuizong.put("heji",jieshaofei + buchongyingyangfei + buchongjieshaofei + lufeiHeji + buhegelufei + guanlifeiHeji + jianyiqi + chaobannian + wangliewailufei);

                    lufeiHuizongList.add(lufeiHuizong);

                }

                //有合并的宣传员
                if (x.getHebingName() != null) {
                    Map lufeiHuizong = new HashMap();
                    Map data = new HashMap();
                    //宣传员本身
                    String name = x.getName();
                    Map<String, Object> model = new HashMap<>();
                    data.put("dateRange",dateRange);
                    data.put("nowdate", new Date());
                    data.put("xuhao", x.getXuhao());

                    model = 获取宣传员统计费用(name,model, sqlManager);

                    lufeiTotalCount = lufeiTotalCount + (Integer)model.get("lufeiCount");
                    guanlifeiTotalCount = guanlifeiTotalCount + (Integer)model.get("guanlifeiCount");
                    jieshaofeiTotalCount = jieshaofeiTotalCount + (Integer)model.get("jieshaofeiCount");
                    buchongjieshaofeiTotalCount = buchongjieshaofeiTotalCount + (Integer)model.get("buchongjieshaofeiCount");
                    buchongyingyangfeiTotalCount = buchongyingyangfeiTotalCount + (Integer)model.get("buchongyingyangfeiCount");
                    buhegelufeiTotalCount = buhegelufeiTotalCount + (Integer)model.get("buhegelufeiCount");
                    jianyiqiTotalCount = jianyiqiTotalCount + (Integer)model.get("jianyiqiCount");
                    chaobannianTotalCount = chaobannianTotalCount + (Integer)model.get("chaobannianCount");
                    qitafeiyongTotalCount = qitafeiyongTotalCount + (Integer)model.get("wangliewailufeiCount");

                    Double jieshaofei = model.get("jieshaofei") != null ? ((宣传员费用登记表)model.get("jieshaofei")).get总金额() : 0D;
                    Double buchongyingyangfei = model.get("buchongyingyangfei") != null ? ((宣传员费用登记表)model.get("buchongyingyangfei")).get总金额() : 0D;
                    Double buhegelufei = ((宣传员费用登记表)model.get("buhegelufei")).get总金额() != null ? ((宣传员费用登记表)model.get("buhegelufei")).get总金额() : 0D;
                    Double jianyiqi = model.get("jianyiqi") != null ? ((宣传员费用登记表)model.get("jianyiqi")).get总金额() : 0D;
                    if (jianyiqi == null) jianyiqi = 0D;
                    Double chaobannian = model.get("chaobannian") != null ? ((宣传员费用登记表)model.get("chaobannian")).get总金额() : 0D;
                    Double buchongjieshaofei = model.get("buchongjieshaofei") != null ? ((宣传员费用登记表)model.get("buchongjieshaofei")).get总金额() : 0D;
                    Double wangliewailufei = model.get("wangliewailufei") != null ? ((宣传员费用登记表)model.get("wangliewailufei")).get总金额() : 0D;
                    Double lufeiHeji = (Double)model.get("lufeiHeji");
                    Double guanlifeiHeji = (Double)model.get("guanlifeiHeji");

                    //被合并的宣传员
                    String hebingName = x.getHebingName();
                    Map<String, Object> model1 = new HashMap<>();

                    model1 = 获取宣传员统计费用(hebingName,model1, sqlManager);

                    lufeiTotalCount = lufeiTotalCount + (Integer)model1.get("lufeiCount");
                    guanlifeiTotalCount = guanlifeiTotalCount + (Integer)model1.get("guanlifeiCount");
                    jieshaofeiTotalCount = jieshaofeiTotalCount + (Integer)model1.get("jieshaofeiCount");
                    buchongjieshaofeiTotalCount = buchongjieshaofeiTotalCount + (Integer)model1.get("buchongjieshaofeiCount");
                    buchongyingyangfeiTotalCount = buchongyingyangfeiTotalCount + (Integer)model1.get("buchongyingyangfeiCount");
                    buhegelufeiTotalCount = buhegelufeiTotalCount + (Integer)model1.get("buhegelufeiCount");
                    jianyiqiTotalCount = jianyiqiTotalCount + (Integer)model1.get("jianyiqiCount");
                    chaobannianTotalCount = chaobannianTotalCount + (Integer)model1.get("chaobannianCount");
                    qitafeiyongTotalCount = qitafeiyongTotalCount + (Integer)model1.get("wangliewailufeiCount");

                    Double jieshaofei1 = model1.get("jieshaofei") != null ? ((宣传员费用登记表)model1.get("jieshaofei")).get总金额() : 0D;
                    Double buchongyingyangfei1 = model1.get("buchongyingyangfei") != null ? ((宣传员费用登记表)model1.get("buchongyingyangfei")).get总金额() : 0D;
                    Double buhegelufei1 = ((宣传员费用登记表)model1.get("buhegelufei")).get总金额() != null ? ((宣传员费用登记表)model1.get("buhegelufei")).get总金额() : 0D;
                    Double jianyiqi1 = model1.get("jianyiqi") != null ? ((宣传员费用登记表)model1.get("jianyiqi")).get总金额() : 0D;
                    if (jianyiqi1 == null) jianyiqi1 = 0D;
                    Double chaobannian1 = model1.get("chaobannian") != null ? ((宣传员费用登记表)model1.get("chaobannian")).get总金额() : 0D;
                    Double buchongjieshaofei1 = model1.get("buchongjieshaofei") != null ? ((宣传员费用登记表)model1.get("buchongjieshaofei")).get总金额() : 0D;
                    Double wangliewailufei1 = model1.get("wangliewailufei1") != null ? ((宣传员费用登记表)model1.get("wangliewailufei1")).get总金额() : 0D;
                    Double lufeiHeji1 = (Double)model1.get("lufeiHeji");
                    Double guanlifeiHeji1 = (Double)model1.get("guanlifeiHeji");

                    data.put("model",model);
                    data.put("model1",model1);

                    //生成宣传费领款单(model,i);
                    生成合并车主路费明细(model1,i);

                    i = i+1;

                    生成宣传费领款单(model,i);
                    生成宣传费领款单(model1,i);

                    String excelTemplate = "excelTemplates/路费费用统计 - 合并车主.xls";
                    try(InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(excelTemplate)) {
                        if(is==null) {
                            throw new PlatformException("模板资源不存在："+excelTemplate);
                        }
                        File f = new File(root + "\\宣传员\\" + i + "-" + name + "-" + hebingName + "-费用.xls");
                        f.getParentFile().mkdirs();
                        FileOutputStream os = new FileOutputStream(f);
                        JxlsUtils.exportExcel(is,os,data);
                    } catch (IOException e) {
                        throw new PlatformException(e.getMessage());
                    }

                    //


                    if (StringUtils.equals(name,"曹建涛")) {
                        name = "曹桂勇";
                    }
                    if (StringUtils.equals(name,"张秀春")) {
                        name = "岳良全";
                    }
                    lufeiHuizong.put("name",name);
                    lufeiHuizong.put("xuhao",x.getXuhao());
                    lufeiHuizong.put("hebingName",hebingName);
                    lufeiHuizong.put("jieshaofei",jieshaofei + jieshaofei1);jieshaofeiTotal = jieshaofeiTotal + jieshaofei + jieshaofei1;
                    lufeiHuizong.put("buchongyingyangfei",buchongyingyangfei + buchongyingyangfei1);buchongyingyangfeiTotal = buchongyingyangfeiTotal + buchongyingyangfei + buchongyingyangfei1;
                    lufeiHuizong.put("buchongjieshaofei",buchongjieshaofei + buchongjieshaofei1);buchongjieshaofeiTotal = buchongjieshaofeiTotal + buchongjieshaofei + buchongjieshaofei1;
                    lufeiHuizong.put("buhegelufei",buhegelufei + buhegelufei1);buhegelufeiTotal = buhegelufeiTotal + buhegelufei + buhegelufei1;
                    lufeiHuizong.put("lufeiHeji",lufeiHeji + lufeiHeji1);lufeiHejiTotal = lufeiHejiTotal + lufeiHeji + lufeiHeji1;
                    lufeiHuizong.put("guanlifeiHeji",guanlifeiHeji + guanlifeiHeji1);guanlifeiHejiTotal = guanlifeiHejiTotal + guanlifeiHeji + guanlifeiHeji1;
                    lufeiHuizong.put("jianyiqi",jianyiqi + jianyiqi1);jianyiqiTotal = jianyiqiTotal + jianyiqi + jianyiqi1;
                    lufeiHuizong.put("chaobannian",chaobannian + chaobannian1);chaobannianTotal = chaobannianTotal + chaobannian + chaobannian1;
                    lufeiHuizong.put("wangliewailufei",wangliewailufei + wangliewailufei1);wangliewailufeiTotal = wangliewailufeiTotal + wangliewailufei + wangliewailufei1;

                    lufeiHuizong.put("heji",(jieshaofei + buchongyingyangfei + buchongjieshaofei + lufeiHeji + buhegelufei + guanlifeiHeji + jianyiqi + chaobannian + wangliewailufei) + (jieshaofei1 + buchongyingyangfei1 + buchongjieshaofei1 + lufeiHeji1 + buhegelufei1 + guanlifeiHeji1 + jianyiqi1 + chaobannian1 + wangliewailufei1));
                    lufeiHuizongList.add(lufeiHuizong);
                }
            }
            String excelTemplate = "excelTemplates/路费费用汇总.xls";
            try(InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(excelTemplate)) {
                if(is==null) {
                    throw new PlatformException("模板资源不存在："+excelTemplate);
                }
                File f = new File(root + "\\宣传员\\0-汇总（" + dateRange + "）" + xcyList.size() + ".xls");
                f.getParentFile().mkdirs();

                FileOutputStream os = new FileOutputStream(f);
                Map<String, Object> model = new HashMap<>();
                model.put("jieshaofeiTotal", jieshaofeiTotal);
                model.put("jianyiqiTotal", jianyiqiTotal);
                model.put("chaobannianTotal", chaobannianTotal);
                model.put("wangliewailufeiTotal", wangliewailufeiTotal);
                model.put("buchongyingyangfeiTotal", buchongyingyangfeiTotal);
                model.put("buchongjieshaofeiTotal", buchongjieshaofeiTotal);
                model.put("lufeiHejiTotal", lufeiHejiTotal);
                model.put("guanlifeiHejiTotal", guanlifeiHejiTotal);
                model.put("buhegelufeiTotal", buhegelufeiTotal);

                model.put("lufeiTotalCount",lufeiTotalCount);
                model.put("guanlifeiTotalCount",guanlifeiTotalCount);
                model.put("jieshaofeiTotalCount",jieshaofeiTotalCount);
                model.put("buchongjieshaofeiTotalCount",buchongjieshaofeiTotalCount);
                model.put("buchongyingyangfeiTotalCount",buchongyingyangfeiTotalCount);
                model.put("buhegelufeiTotalCount",buhegelufeiTotalCount);
                model.put("jianyiqiTotalCount",jianyiqiTotalCount);
                model.put("chaobannianTotalCount",chaobannianTotalCount);
                model.put("qitafeiyongTotalCount",qitafeiyongTotalCount);

                model.put("lufeiHuizongList", lufeiHuizongList);
                model.put("nowdate", new Date());
                model.put("dateRange",dateRange);
                JxlsUtils.exportExcel(is,os,model);
            } catch (IOException e) {
                throw new PlatformException(e.getMessage());
            }
        }
    }


    public static Map 获取宣传员统计费用(String name, Map model, SQLManager sqlManager) {
        Double jieshaofei = 0D;
        Double buchongyingyangfei = 0D;
        Double buhegelufei = 0D;
        Double lufeiHeji = 0D;
        Double xuanchuanfeiHeji = 0D;
        Double guanlifeiHeji = 0D;

        Double jianyiqi = 0D;
        Double chaobannian = 0D;
        Double buchongjieshaofei = 0D;
        Double wangliewailufei = 0D;


        List<路费差额统计> lufeiList = sqlManager.execute(new SQLReady("select * from 路费审核 where 宣传员姓名 = ? and 费用类型 = ?", new Object[] {name,"交通补贴"}),路费差额统计.class);
        List<路费差额统计> lufeiHejiList = sqlManager.execute(new SQLReady("select sum(费用金额) as 总路费,count(*) as count from 路费审核 where 宣传员姓名 = ? and 费用类型 = ?", new Object[] {name,"交通补贴"}),路费差额统计.class);
        List<路费差额统计> guanlifeiHejiList = sqlManager.execute(new SQLReady("select sum(管理费) as 管理费合计,count(*) as count from 路费审核 where 宣传员姓名 = ? and 费用类型 = ?", new Object[] {name,"交通补贴"}),路费差额统计.class);
        List<路费差额统计> lufeiZongshu = sqlManager.execute(new SQLReady("select count(*) as count from 路费审核 where 宣传员姓名 = ? and 费用类型 = ?", new Object[] {name,"交通补贴"}),路费差额统计.class);

        List<路费差额统计> 宣传费用List = sqlManager.execute(new SQLReady("select * from 路费审核 where 宣传员姓名 = ? and 费用类型 = ?", new Object[] {name,"宣传费用"}),路费差额统计.class);
        List<路费差额统计> 宣传费用HejiList = sqlManager.execute(new SQLReady("select sum(费用金额) as 总路费,count(*) as count from 路费审核 where 宣传员姓名 = ? and 费用类型 = ?", new Object[] {name,"宣传费用"}),路费差额统计.class);
        if (宣传费用HejiList != null && 宣传费用HejiList.size() == 1) {
            if (宣传费用HejiList.get(0).get总路费() != null) {
                xuanchuanfeiHeji = 宣传费用HejiList.get(0).get总路费();
            }
        }
        model.put("xuanchuanfeiHeji", xuanchuanfeiHeji);
        model.put("xuanchuanfeiZongshu", 宣传费用List.size());

        model.put("lufeiCount", 0);
        model.put("guanlifeiCount", 0);
        model.put("jieshaofeiCount",0);
        model.put("buchongjieshaofeiCount",0);
        model.put("buchongyingyangfeiCount",0);
        model.put("buhegelufeiCount",0);
        model.put("jianyiqiCount",0);
        model.put("chaobannianCount",0);
        model.put("wangliewailufeiCount",0);

        model.put("lufeiHeji",0D);
        model.put("guanlifeiHeji",0D);

        if (lufeiHejiList != null && lufeiHejiList.size() == 1) {
            if (lufeiHejiList.get(0).get总路费() != null) {
                lufeiHeji = lufeiHejiList.get(0).get总路费();
                model.put("lufeiHeji", lufeiHeji);
                model.put("lufeiCount", lufeiHejiList.get(0).getCount());
            }
        }


        if (guanlifeiHejiList != null && guanlifeiHejiList.size() == 1) {
            if (guanlifeiHejiList.get(0).get管理费合计() != null) {
                guanlifeiHeji = guanlifeiHejiList.get(0).get管理费合计();
                model.put("guanlifeiHeji", guanlifeiHeji);
                model.put("guanlifeiCount", guanlifeiHejiList.get(0).getCount());
            }
        }


        if (lufeiZongshu.get(0).getCount() != 0) {
            model.put("lufeiZongshu", lufeiZongshu.get(0).getCount());
        }
        try {
            List<宣传员费用登记表> 宣传费_list = sqlManager.execute(new SQLReady("select 金额,count(*) as count,sum(金额) as 总金额 from 宣传员费用登记表 where 领取人 = ? and 类型='宣传费' and 日期 between ? and ? ", new Object[] {name,dateRangeStart, dateRangeEnd}), 宣传员费用登记表.class);
            if (宣传费_list != null && 宣传费_list.size() == 1) {
                model.put("jieshaofei",宣传费_list.get(0));
                if (宣传费_list.get(0).get总金额() != null) {
                    jieshaofei = 宣传费_list.get(0).get总金额();
                    model.put("jieshaofeiCount",宣传费_list.get(0).getCount());
                }
            } else if (宣传费_list.size() > 1) {
                File f = new File(root + "\\宣传员\\error.txt");
                FileUtils.write(f,name + "介绍费_费用出错|",true);
            }

            List<宣传员费用登记表> 补充介绍费 = sqlManager.execute(new SQLReady("select 金额,count(*) as count,sum(金额) as 总金额 from 宣传员费用登记表 where 领取人 = ? and 金额=40 and 类型!='不合格路费' and 日期 between ? and ? group by 金额 ", new Object[] {name,dateRangeStart, dateRangeEnd}),宣传员费用登记表.class);
            if (补充介绍费 != null && 补充介绍费.size() == 1) {
                model.put("buchongjieshaofei",补充介绍费.get(0));

                if (补充介绍费.get(0).get总金额() != null) {
                    buchongjieshaofei = 补充介绍费.get(0).get总金额();
                    model.put("buchongjieshaofeiCount",补充介绍费.get(0).getCount());
                }
            } else if (补充介绍费.size() > 1) {
                File f = new File(root + "\\宣传员\\error.txt");
                FileUtils.write(f,name + "补充介绍费出错|",true);
            }

            List<宣传员费用登记表> 补充营养_100_list = sqlManager.execute(new SQLReady("select 金额,count(*) as count,sum(金额) as 总金额 from 宣传员费用登记表 where 领取人 = ? and 金额=100 and 日期 between ? and ? group by 金额 ", new Object[] {name,dateRangeStart, dateRangeEnd}),宣传员费用登记表.class);
            if (补充营养_100_list != null && 补充营养_100_list.size() == 1) {
                model.put("buchongyingyangfei",补充营养_100_list.get(0));

                if (补充营养_100_list.get(0).get总金额() != null) {
                    buchongyingyangfei = 补充营养_100_list.get(0).get总金额();
                    model.put("buchongyingyangfeiCount",补充营养_100_list.get(0).getCount());
                }
            } else if (补充营养_100_list.size() > 1) {
                File f = new File(root + "\\宣传员\\error.txt");
                FileUtils.write(f,name + "补充营养费用出错|",true);
            }

            //该项主要针对王利曲堤5人补1人的政策
            List<宣传员费用登记表> 不合格路费 = sqlManager.execute(new SQLReady("select 金额,count(*) as count,sum(金额) as 总金额 from 宣传员费用登记表 where 领取人 = ? and 类型='不合格路费' and 日期 between ? and ? ", new Object[] {name,dateRangeStart, dateRangeEnd}),宣传员费用登记表.class);
            if (不合格路费 != null && 不合格路费.size() == 1) {
                model.put("buhegelufei",不合格路费.get(0));

                if (不合格路费.get(0).get总金额() != null) {
                    buhegelufei = 不合格路费.get(0).get总金额();
                    model.put("buhegelufeiCount",不合格路费.get(0).getCount());
                }
            } else if (不合格路费.size() > 1) {
                File f = new File(root + "\\宣传员\\error.txt");
                FileUtils.write(f,name + "其他费用|",true);
            }

            List<宣传员费用登记表> 检疫期费用 = sqlManager.execute(new SQLReady("select 金额,count(*) as count,sum(金额) as 总金额 from 宣传员费用登记表 where 领取人 = ? and 金额=30 and 类型!='不合格路费' and 日期 between ? and ? group by 金额 ", new Object[] {name,dateRangeStart, dateRangeEnd}), 宣传员费用登记表.class);
            if (检疫期费用 != null && 检疫期费用.size() == 1) {
                model.put("jianyiqi",检疫期费用.get(0));

                if (检疫期费用.get(0).get总金额() != null) {
                    jianyiqi = 检疫期费用.get(0).get总金额();
                    model.put("jianyiqiCount",检疫期费用.get(0).getCount());
                }
            } else if (检疫期费用.size() > 1) {
                File f = new File(root + "\\宣传员\\error.txt");
                FileUtils.write(f,name + "检疫期费用出错|",true);
            }

            List<宣传员费用登记表> 超半年费用 = sqlManager.execute(new SQLReady("select 金额,count(*) as count,sum(金额) as 总金额 from 宣传员费用登记表 where 领取人 = ? and 金额=50 and 类型!='不合格路费' and 日期 between ? and ? group by 金额 ", new Object[] {name,dateRangeStart, dateRangeEnd}),宣传员费用登记表.class);
            if (超半年费用 != null && 超半年费用.size() == 1) {
                model.put("chaobannian",超半年费用.get(0));
                model.put("chaobannianCount",0);
                if (超半年费用.get(0).get总金额() != null) {
                    chaobannian = 超半年费用.get(0).get总金额();
                    model.put("chaobannianCount",超半年费用.get(0).getCount());
                }
            } else if (超半年费用.size() > 1) {
                File f = new File(root + "\\宣传员\\error.txt");
                FileUtils.write(f,name + "超半年费用出错|",true);
            }

            //该项主要针对王利曲堤5人补1人的政策
            List<宣传员费用登记表> 王利额外路费 = sqlManager.execute(new SQLReady("select 金额,count(*) as count,sum(金额) as 总金额 from 宣传员费用登记表 where 领取人 = ? and (金额=48) and 类型='王利补路费48' and 日期 between ? and ? group by 金额 ", new Object[] {name,dateRangeStart, dateRangeEnd}),宣传员费用登记表.class);
            if (王利额外路费 != null && 王利额外路费.size() == 1) {
                model.put("wangliewailufei",王利额外路费.get(0));

                if (王利额外路费.get(0).get总金额() != null) {
                    wangliewailufei = 王利额外路费.get(0).get总金额();
                    model.put("wangliewailufeiCount",王利额外路费.get(0).getCount());
                }
            } else if (王利额外路费.size() > 1) {
                File f = new File(root + "\\宣传员\\error.txt");
                FileUtils.write(f,name + "王利额外路费出错|",true);
            }

            if (lufeiList.size() == 0) {
                路费差额统计 o = new 路费差额统计();
                lufeiList.add(o);
            }

            if (lufeiList.size() == 0) {
                路费差额统计 o = new 路费差额统计();
                lufeiList.add(o);
            }
            model.put("feiyongheji",  buhegelufei
                    + lufeiHeji
                    + guanlifeiHeji
                    + jieshaofei
                    + buchongyingyangfei
                    + jianyiqi
                    + chaobannian
                    + buchongjieshaofei
                    + wangliewailufei);
            model.put("lufeiList", lufeiList);
            model.put("xuanchuanfeiList", 宣传费用List);
            model.put("name",name);
            model.put("nowdate", new Date());
        } catch (IOException e) {
            e.printStackTrace();
        }

        return model;
    }


    public static void 生成合并车主路费明细(Map<String, Object> data, int sn){
        String excelTemplate = "excelTemplates/合并车主路费明细.xls";
        try(InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(excelTemplate)) {
            if(is==null) {
                throw new PlatformException("模板资源不存在："+excelTemplate);
            }
            String name = (String) data.get("name");
            File f = new File(root + "\\宣传员\\合并车主路费明细\\" + sn + "-" + name + "-领款单.xls");
            f.getParentFile().mkdirs();
            FileOutputStream os = new FileOutputStream(f);
            JxlsUtils.exportExcel(is,os,data);
        } catch (IOException e) {
            throw new PlatformException(e.getMessage());
        }
    }

    public static void 生成宣传费领款单(Map<String, Object> data, int sn){
        List xuanchuanfeiList = (List)data.get("xuanchuanfeiList");
        if (xuanchuanfeiList != null && xuanchuanfeiList.size() > 0) {
            String excelTemplate = "excelTemplates/宣传费领款单.xls";
            try(InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(excelTemplate)) {
                if(is==null) {
                    throw new PlatformException("模板资源不存在："+excelTemplate);
                }
                String name = (String) data.get("name");
                File f = new File(root + "\\宣传员\\" + sn + "-" + name + "-宣传费领款单.xls");
                f.getParentFile().mkdirs();
                FileOutputStream os = new FileOutputStream(f);
                JxlsUtils.exportExcel(is,os,data);
            } catch (IOException e) {
                throw new PlatformException(e.getMessage());
            }
        }
    }
}
